﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using SMSModels;
using System.Data;
using System.Data.SQLite;

namespace DAL.QueryStatistics
{
    public class StorageQueryService
    {
        /// <summary>
        /// 获取库存表dataset
        /// </summary>
        /// <returns></returns>
        public DataSet GetDataSet()
        {
            string sql = "select 库存表.货物编号,货物名称,规格,计量单位,库存表.仓库ID,仓库信息表.仓库名称,库存表.供货商ID,供货商信息表.供货商名称,入库单价,库存数量,库存表.备注 from 库存表,货物档案表,仓库信息表,供货商信息表 where 库存表.货物编号=货物档案表.货物编号 and 库存表.仓库ID=仓库信息表.仓库ID and 库存表.供货商ID=供货商信息表.供货商ID";
            return SQLHelper.GetDataSet(sql, "storageInfo");
        }


        /// <summary>
        /// 获取现有库存list
        /// </summary>
        /// <param name="goodsID"></param>
        /// <returns></returns>
        public List<Storage> GetCurrentStorageList()
        {
            string sql = "select 库存表.货物编号,货物名称,规格,计量单位,库存表.仓库ID,仓库信息表.仓库名称,库存表.供货商ID,供货商信息表.供货商名称,入库单价,库存数量,库存表.备注 from 库存表,货物档案表,仓库信息表,供货商信息表 where 库存表.货物编号=货物档案表.货物编号 and 库存表.仓库ID=仓库信息表.仓库ID and 库存表.供货商ID=供货商信息表.供货商ID";
            SQLiteDataReader rd = SQLHelper.GetReader(sql);
            List<Storage> list = new List<Storage>();
            while (rd.Read())
            {
                list.Add(new Storage
                {
                    GoodsID = rd["货物编号"].ToString(),
                    GoodsName = rd["货物名称"].ToString(),
                    Spec = rd["规格"].ToString(),
                    Units = rd["计量单位"].ToString(),
                    StoreID = Convert.ToInt32(rd["仓库ID"]),
                    StoreName = rd["仓库名称"].ToString(),
                    SupplierID = Convert.ToInt32(rd["供货商ID"]),
                    SupplierName = rd["供货商名称"].ToString(),
                    StorageNum=Convert.ToInt32(rd["库存数量"]),
                });
            }
            rd.Close();
            return list;
        }



        /// <summary>
        /// 选择货物编号时查询对应编号货物信息
        /// </summary>
        /// <param name="goodsID">编号编号</param>
        /// <returns></returns>
        public List<Storage> GetListOnSelectGoodsID(string goodsID)
        {
            string sql = string.Format("select * from (select 库存表.货物编号,货物名称,规格,计量单位,库存表.仓库ID,仓库信息表.仓库名称,库存表.供货商ID,供货商信息表.供货商名称,入库单价,库存数量,库存表.备注 from 库存表,货物档案表,仓库信息表,供货商信息表 where 库存表.货物编号=货物档案表.货物编号 and 库存表.仓库ID=仓库信息表.仓库ID and 库存表.供货商ID=供货商信息表.供货商ID) a where a.货物编号='{0}'", goodsID);
            SQLiteDataReader rd= SQLHelper.GetReader(sql);
            List<Storage> list = new List<Storage>();
            while (rd.Read())
            {
                list.Add(new Storage { 
                    GoodsID=rd["货物编号"].ToString(),
                    GoodsName = rd["货物名称"].ToString(),
                    Spec=rd["规格"].ToString(),
                    Units=rd["计量单位"].ToString(),
                    StoreID=Convert.ToInt32(rd["仓库ID"]),
                    StoreName = rd["仓库名称"].ToString(),
                    SupplierID= Convert.ToInt32(rd["供货商ID"]),
                    SupplierName = rd["供货商名称"].ToString(),
                });
            }
            return list;
        }


        public DataSet QueryStorageInfoOfKeyWords(string queryType, string queryKeyWords)
        {
            string sql = "select * from (select 库存表.货物编号,货物名称,规格,计量单位,库存表.仓库ID,仓库信息表.仓库名称,库存表.供货商ID,供货商信息表.供货商名称,入库单价,库存数量,库存表.备注 from 库存表,货物档案表,仓库信息表,供货商信息表 where 库存表.货物编号=货物档案表.货物编号 and 库存表.仓库ID=仓库信息表.仓库ID and 库存表.供货商ID=供货商信息表.供货商ID) b";
            if (queryType == "货物编号")  //以货物ID查询
            {
                sql += string.Format(" where b.货物编号='{0}'", queryKeyWords);
            }
            if (queryType == "货物名称")    //以货物名称查询
            {
                sql += string.Format(" where b.货物名称 like '%{0}%'", queryKeyWords);
            }
            if (queryType == "仓库名称")    //以仓库名称查询
            {
                sql += string.Format(" where b.仓库名称='{0}'", queryKeyWords);
            }
            return SQLHelper.GetDataSet(sql, "queryResult");
        }
    }
}
